const config = require('../config');

const mysql = require('../db/mysql57');
const minio = require('../db/minio');

const POST_CONTENT = `c.id as id,
c.uid as uid,
u.username as username,
u.avaterId as avaterId,
c.title as title,
c.content as content,
c.imgIdList as imgIdList,
ct.name as type,
DATE_FORMAT(c.createTime, '%Y-%m-%d %H:%i:%s') as createTime,
(select COUNT(1) from CommunityLike where cid = c.id) as likeCount,
(select COUNT(1) from CommunityComment where cid = c.id) as commentCount,
(select COUNT(1) from CommunityView where cid = c.id) as viewCount,
(select COUNT(1) from CommunityLike where cid = c.id and uid = ? LIMIT 1) as isLike`;

const communityDao = {
    /**
     * 获取动态图片预处理URL接口
     */
    async getCommentImgUrl(imgId) {
        let browser;
        let uri;
        let sql = `select browser, uri from CommunityImage where id = ? and status = ? LIMIT 1`;
        let data = [imgId, '已完成'];
        let result = await mysql.sqlExec(sql, data);
        if (result.length) {
            browser = result[0].browser;
            uri = result[0].uri;
        } else {
            return "";
        }
        // 获取图片预处理URL
        try {
            let url = await minio.getDownloadUrl(browser, uri, config.minio.downloadUrlEnableTime);
            if (!url.success) {
                return "";
            }
            return url.url;
        } catch (e) {
            return "";
        }
    },
    /**
     * 获取动态类型
     */
    getPostTypes() {
        let sql = `select id, name from CommunityType`;
        return mysql.sqlExec(sql, []);
    },
    /**
     * 预发布动态
     */
    async preparePost(uid, title, content, imgCount, type) {
        let connection;
        let result;
        let communityId;
        let imgIdList = []
        let communityImageList = [];
        let browser = config.minio.postBrower;
        let expireTime = config.minio.uploadUrlEnableTime;
        // 开启事务
        try {
            connection = await mysql.startTransaction();
        } catch (error) {
            throw error;
        }
        // 插入帖子
        try {
            let sql = `INSERT INTO Community(
                    uid, title, content, typeId, status
                ) VALUES (?, ?, ?, (
                    select id from CommunityType where name = ? LIMIT 1
                ), ?)`;
            let data = [ uid, title, content, type, '预发布' ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
            communityId = result.insertId;
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 插入帖子图片
        try {
            let sql = `INSERT INTO CommunityImage(cid, browser, uri, status) VALUES (?, ?, ?, ?);`;
            let data = [ communityId, browser, '', '待上传' ];
            for (let i = 0; i < imgCount; i++) {
                result = await mysql.sqlExecInTransaction(connection, sql, data);
                communityImageList.push({ id: result.insertId, url: '' });
                imgIdList.push(result.insertId);
            }
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 将图片id列表插入数据库
        try {
            let sql = `UPDATE Community SET imgIdList = ? WHERE id = ?`;
            let data = [ JSON.stringify(imgIdList), communityId ];
            result = await mysql.sqlExecInTransaction(connection, sql, data);
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 向 minio 申请 预签名URL
        try {
            for (let i = 0; i < communityImageList.length; i++) {
                let item = communityImageList[i];
                result = await minio.getUploadUrl(browser, `${item.id}.jpg`, expireTime);
                if (result.success) {
                    communityImageList[i].url = result.url;
                } else {
                    throw "获取 上传预签名URL 失败";
                }
            }
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 提交事务
        try {
            mysql.commitTransaction(connection);
        } catch (err) {
            await mysql.rollbackTransaction(connection);
            throw err;
        }
        // 返回数据
        return { communityId, communityImageList };
    },
    /**
     * 标记发布动态
     */
    finalizePost(uid, postId) {
        let sql = `UPDATE Community SET status = ? WHERE id = ? AND uid = ?`;
        let data = ['已发布', postId, uid];
        return mysql.sqlExec(sql, data);
    },
    /**
     * 标记图片上传完毕
     */
    finalizeImg(uid, imgId) {
        let sql = `
            UPDATE CommunityImage as c
            SET c.uri = ?, c.status = '已完成'
            WHERE c.id = ? 
                AND (
                    SELECT uid FROM Community WHERE id = c.cid
                ) = ?`;
        let data = [ `${imgId}.jpg`, imgId, uid ];
        return mysql.sqlExec(sql, data);
    },
    /**
     * 删除动态
     */
    delPost(uid, postId) {
        let sql = `UPDATE Community SET isDel = ? WHERE id = ? AND uid = ?`;
        let data = ['true', postId, uid];
        return mysql.sqlExec(sql, data);
    },
    /**
     * 获取推荐动态
     */
    getRecommendPosts(uid, postId) {
        let sql = `SELECT
                    ${POST_CONTENT}
                FROM Community as c
                LEFT JOIN User as u ON c.uid = u.id
                LEFT JOIN CommunityType as ct ON c.typeId = ct.id
                WHERE `;
        let data;
        if (postId) {
            sql += `c.id < ? AND c.isDel = ? AND c.status = ? ORDER BY c.id DESC LIMIT ?`;
            data = [ uid, postId, 'false', '已发布', config.community.getRecommendPostCount ];
        } else {
            sql += `c.isDel = ? AND c.status = ? ORDER BY c.id DESC LIMIT ?`;
            data = [ uid, 'false', '已发布', config.community.getRecommendPostCount ];
        }
        return mysql.sqlExec(sql, data);
    },
    /**
     * 按照类型懒加载推荐动态
     */
    getRecommendPostsByType(uid, postId, typeId) {
        let sql = `SELECT
                    ${POST_CONTENT}
                FROM Community as c
                LEFT JOIN User as u ON c.uid = u.id
                LEFT JOIN CommunityType as ct ON c.typeId = ct.id
                WHERE `;
        let data;
        if (postId) {
            sql += `c.id < ? AND c.isDel = ? AND c.status = ? AND c.typeId = ? ORDER BY c.id DESC LIMIT ?`;
            data = [ uid, postId, 'false', '已发布', typeId, config.community.getRecommendPostCount ];
        } else {
            sql += `c.isDel = ? AND c.status = ? AND c.typeId = ? ORDER BY c.id DESC LIMIT ?`;
            data = [ uid, 'false', '已发布', typeId, config.community.getRecommendPostCount ];
        }
        return mysql.sqlExec(sql, data);
    },
    /**
     * 获取用户自己的动态
     */
    getSelfPosts(uid, postId) {
        let sql = `SELECT
                    ${POST_CONTENT}
                FROM Community as c
                LEFT JOIN User as u ON c.uid = u.id
                LEFT JOIN CommunityType as ct ON c.typeId = ct.id
                WHERE u.id = ? AND `;
        let data;
        if (postId) {
            sql += `c.id < ? AND c.isDel = ? AND c.status = ? ORDER BY c.id DESC LIMIT ?`;
            data = [ uid, uid, postId, 'false', '已发布', config.community.getRecommendPostCount ];
        } else {
            sql += `c.isDel = ? AND c.status = ? ORDER BY c.id DESC LIMIT ?`;
            data = [ uid, uid, 'false', '已发布', config.community.getRecommendPostCount ];
        }
        return mysql.sqlExec(sql, data);
    },
    /**
     * 获取单个动态
     * 这个接口也少了一个功能 需要记录一次阅读量，不过这个逻辑不写在 这个函数里
     */
    getPost(uid, postId) {
        let sql = `SELECT
                ${POST_CONTENT}
            FROM Community as c
            LEFT JOIN User as u ON c.uid = u.id
            LEFT JOIN CommunityType as ct ON c.typeId = ct.id
            WHERE c.id = ? AND isDel = ? AND status = ? LIMIT 1`;
        let data = [ uid, postId, 'false', '已发布' ];
        return mysql.sqlExec(sql, data);
    },
    /**
     * 点赞或取消点赞动态
     */
    likePost(id, like, uid) {
        let sql;
        let data;
        if (like) {
            sql = `INSERT INTO CommunityLike (cid, uid) (select ?, ? where NOT EXISTS (SELECT 1 FROM CommunityLike WHERE cid = ? AND uid = ?))`;
            data = [ id, uid, id, uid ];
        } else {
            sql = `DELETE FROM CommunityLike WHERE cid = ? AND uid = ?`;
            data = [ id, uid ];
        }
        return mysql.sqlExec(sql, data);
    },
    /**
     * 评论动态
     */
    postComment(id, content, uid) {
        let sql = `INSERT INTO CommunityComment (
                cid, uid, content
            ) VALUES (?, ?, ?)`;
        let data = [ id, uid, content ];
        return mysql.sqlExec(sql, data);
    },
    /**
     * 回复评论
     */
    postCommentReply(id, content, uid) {
        let sql = `INSERT INTO CommunityCommentReply (
                cid, uid, content
            ) VALUES (?, ?, ?)`;
        let data = [ id, uid, content ];
        return mysql.sqlExec(sql, data);
    },
    /**
     * 获取动态的评论
     */
    getPostComment(postId, lastCommentId) {
        let sql = `SELECT
                cc.id as id,
                cc.uid as uid,
                u.username as username,
                u.avaterId as avatarId,
                cc.content as content,
                DATE_FORMAT(cc.createTime, '%Y-%m-%d %H:%i:%s') as createTime,
                (select COUNT(1) from CommunityCommentReply where cid = cc.id) as replyCount
            FROM CommunityComment as cc
            LEFT JOIN User as u ON cc.uid = u.id
            WHERE cc.cid = ? ${ lastCommentId ? `AND cc.id < ?` : '' }
            ORDER BY cc.createTime DESC
            LIMIT ${ config.community.getPostCommentCount }`;
        let data = lastCommentId ? [ postId, lastCommentId ] : [ postId ];
        return mysql.sqlExec(sql, data);
    },
    getPostCommentReply(postCommentId, lastCommentId) {
        let sql = `SELECT
                ccr.id as id,
                ccr.uid as uid,
                u.username as username,
                u.avaterId as avatarId,
                ccr.content as content,
                DATE_FORMAT(ccr.createTime, '%Y-%m-%d %H:%i:%s') as createTime
            FROM CommunityCommentReply as ccr
            LEFT JOIN User as u ON ccr.uid = u.id
            WHERE ccr.cid = ? ${ lastCommentId ? `AND ccr.id < ?` : '' }
            ORDER BY ccr.createTime DESC
            LIMIT ${ config.community.getPostCommentReplyCount }`;
        let data = lastCommentId ? [ postCommentId, lastCommentId ] : [ postCommentId ];
        return mysql.sqlExec(sql, data);
    },
    deleteComment(uid, id) {
        let sql = `DELETE FROM CommunityComment WHERE id = ? AND uid = ?`;
        let data = [ id, uid ];
        return mysql.sqlExec(sql, data);
    },
    deleteCommentReply(uid, id) {
        let sql = `DELETE FROM CommunityCommentReply WHERE id = ? AND uid = ?`;
        let data = [ id, uid ];
        return mysql.sqlExec(sql, data);
    }
};

module.exports = communityDao;
